from iFinDPy import *
from DataProcess.Ifindfunctions import IFind_DailyQuote
import chardet
import datetime
import pandas as pd

# ---更新公募基金列表---
def Download_MutualFund_List(database, cold_start=False):
    # 数据池-免费用户专用_板块成分-同花顺代码;证券名称-iFinD数据接口
    datetime_now = datetime.datetime.now()
    s_datetime_now = datetime_now.strftime('%Y-%m-%d')
    b_data = THS_DataPool('block', s_datetime_now + ';051001039', 'thscode:Y', True) # 含未能成立已到期
    str1 = str(b_data, 'utf-8')
    data = eval(str1)
    #
    symbol_list = data["tables"][0]["table"]["THSCODE"]
    print(symbol_list)
    #
    # i = 0
    # for symbol in symbol_list:
    #     i += 1
    #     if i < 10200:
    #         continue
    #     # if symbol in ["005966.OF", "005965.OF"]:
    #     #     continue
    #     print(i, symbol)
    #     Download_MutualFund_Info(database, [symbol])
    #     a = 0

    # ---新建基金条目---
    # ---太多崩溃，分批提取---
    step = 100
    for i in range(0, len(symbol_list), step):
        # if i < 500:
        #     continue
        #
        print("Download_MutualFund_List", i, "to", i + step)
        symbol_list_tmp = symbol_list[i:i + step]

        # if "010885.OF" not in symbol_list:
        #     continue

        #
        # skip_symbols = ["005966.OF", "005965.OF"]
        # for skip_symbol in skip_symbols:
        #     if skip_symbol in symbol_list_tmp:
        #         symbol_list_tmp.remove(skip_symbol)

        Download_MutualFund_Info(database, symbol_list_tmp)
        a = 0


# ---更新公募基金全部细节---
def Download_MutualFund_Info(database, symbols):
    #
    str_symbols = ",".join(symbols)

    fields_map = {}
    # ---基本要素---
    fields_map["ths_fund_short_name_fund"] = "description"  # 基金简称
    fields_map["ths_fund_full_name_fund"] = "fullname"  # 基金全称
    fields_map["ths_fund_code_fund"] = "symbol" # 基金代码
    fields_map["ths_fund_thscode_fund"] = "symbol"  # 基金同花顺代码
    # #
    fields_map["ths_fund_supervisor_fund"] = "manage_company" # 基金管理人
    fields_map["ths_fund_mandator_fund"] = "custodian_bank" # 基金托管人
    # fields_map["ths_fund_sponsor_related_org_fund"] = # 基金发起人

    fields_map["ths_invest_objective_fund"] = "invest_object"  # 投资目标
    # fields_map["ths_invest_socpe_fund"] = "invest_scope"  # 投资范围
    # ---基金分类---
    fields_map["ths_fund_type_fund"] = "fund_type"  # 基金类型
    # fields_map["ths_fund_invest_type_fund"] =  # 基金投资类型
    fields_map["ths_invest_type_first_classi_fund"] = "invest_type1" # 投资类型(一级分类)
    fields_map["ths_invest_type_second_classi_fund"] = "invest_type2"  # 投资类型(二级分类)
    # ---重要日期---
    fields_map["ths_fund_establishment_date_fund"] = "datetime1"  # 基金成立日
    fields_map["ths_fund_expiry_date_fund"] = "datetime2"  # 基金到期日
    # ---基金经理相关---
    fields_map["ths_fund_manager_current_fund"] = "Manager_Name"  # 基金经理(现任)
    #
    fields_map["ths_tracking_index_thscode_fund"] = "tracking"  # 跟踪标的
    fields_map["ths_related_etf_thscode_fund"] = "linked_etf"  # 连接ETF
    #
    request_fields = list(fields_map.keys())
    str_request_fields = ";".join(request_fields)
    #
    params = []
    for i in range(len(request_fields)):
        params.append("")
    str_params = ";".join(params)

    #
    b_data = THS_BasicData(str_symbols, str_request_fields, str_params, True)
    try:
        guess_encoding = chardet.detect(b_data)
        str1 = str(b_data, guess_encoding["encoding"])
    except:
        str1 = str(b_data, "gbk")

    try:
        data = json.loads(str1)
    except:
        str1 = str1.strip()
        str1 = str1.replace('\\n', '').replace('\\r', '')
        str1 = str1.replace('\\', '')

        # data = eval(str1)
        # print(str1)
        data = json.loads(str1)

    # ---Save to DataBase---
    documents = []
    for symbol_data in data["tables"]:
        symbol = symbol_data["thscode"]
        content = symbol_data["table"]
        #
        document = {}
        for field, values in content.items():
            database_field = fields_map.get(field)
            document[database_field] = values[0]
            if document[database_field] == "":
                document[database_field] = None

        # 处理datetime1
        try:
            document["datetime1"] = datetime.datetime.strptime(str(document["datetime1"]), "%Y%m%d")
        except:
            document["datetime1"] = None

        # 处理datetime2
        if document.get("datetime2") == None:
            document["datetime2"] = datetime.datetime(2100, 1, 1)
        else:
            try:
                document["datetime2"] = datetime.datetime.strptime(str(document["datetime2"]), "%Y%m%d")
            except:
                document["datetime2"] = None
        #
        document["symbol"] = symbol
        document["Currency"] = "CNY"
        document["Key2"] = symbol
        document["instrument_type"] = "MutualFund"
        document["datetime"] = document["date"] = document["datetime1"]
        document["setup_date"] = document["datetime1"]
        document["maturity_date"] = document["datetime2"]
        #
        print("Update New MutualFund", symbol)
        documents.append(document)
        # database.Upsert("Instruments", "MutualFund", {"Symbol": symbol}, dataObject)
        kkwood = 1
    #
    database.Upsert_Many("Instruments", "MutualFund", {}, documents)
    a = 0


def Download_MutualFund_Daily_Quote(database, datetime1=None, datetime2=None, start_index=0):
    instruments = database.Get_Instruments(instrument_type="MutualFund")
    i = 0
    for instrument in instruments:
        i += 1
        if i < start_index:
            continue
        #
        symbol = instrument["symbol"]
        print("Download MutalFund Daily Quote", i, symbol)
        if datetime1 == None:
            tmp_datetime1 = instrument["datetime1"]
        else:
            tmp_datetime1 = datetime1
        #
        if datetime2 == None:
            tmp_datetime2 = datetime.datetime.now()
        else:
            tmp_datetime2 = datetime2
        #
        tmp_datetime1 = tmp_datetime1.strftime('%Y-%m-%d')
        tmp_datetime2 = tmp_datetime2.strftime('%Y-%m-%d')
        res = IFind_DailyQuote(symbol=symbol, instrument_type="MutualFund", datetime1=tmp_datetime1, datetime2=tmp_datetime2)
        database.Upsert_Many("financial_data", "mutualfund_dailybar", {}, res)
        a = 0


# ---基金报表中的持仓明细---
def Download_MutualFund_Positions(database, symbol, report_date, release_date=None):
    #
    print("Download_MutualFund_Positions", symbol, report_date.date())
    #
    fields_map = {}
    #
    # fields_map["ths_held_stock_num_eorp_fund"] = ""  # 报告期末持有股票个数(中报、年报)
    fields_map["ths_top_held_stock_thscode_fund"] = "symbol"  # 重仓股同花顺代码
    fields_map["ths_top_held_num_fund"] = "qty"  # 重仓股持股数量
    fields_map["ths_top_held_mv_fund"] = "equity"  # 重仓股持股市值
    fields_map["ths_top_stock_mv_to_fnv_fund"] = "ratio"  # 重仓股市值占基金资产净值比
    fields_map["ths_top_held_change_fund"] = "qty_change"  # 重仓股持仓变动
    #
    request_fields = list(fields_map.keys())
    str_request_fields = ";".join(request_fields)
    #
    s_report_date = report_date.strftime('%Y%m%d')
    s_report_date_2 = report_date.strftime('%Y-%m-%d')
    #
    for stock_i in range(10):
        # 构造参数
        params = []
        stock_i_2 = stock_i + 1
        for i in range(len(request_fields)):
            params.append(s_report_date + "," + str(stock_i_2))
        str_params = ";".join(params)
        #
        b_data = THS_BasicData(symbol, str_request_fields, str_params, True)
        str1 = str(b_data, "gbk")
        data = json.loads(str1)
        #
        positions = []
        for table in data["tables"]:
            data = table["table"]
            document = {}
            document["mutualfund"] = symbol
            document["report_date"] = report_date
            document["DateTime"] = document["Date"] = document["release_date"] = release_date
            #
            for field, value in data.items():
                mapped_field = fields_map[field]
                value0 = value[0]
                document[mapped_field] = value0
            #
            document["Key2"] = symbol + "_" + s_report_date_2 + "_" + document["symbol"]
            positions.append(document)
            database.Upsert_Many("MutualFund", "Positions", [], positions)
        a = 0
        pass


def Automatic_Download_MutualFund(database, datetime2, startIndex=0):
    # datetime1 = datetime.datetime(2015, 1, 1)
    #
    # # ---更新共募集金列表，（新基金上市，旧下市信息）---
    # DataProcess.Download_MutualFund_List(database, forceToUpdate=True)
    #
    # # ---更新基金公司信息---
    # Download_MutualFund_Company_Info(database)
    #
    # # ---更新公募基金经理信息--- 未完成
    # # Download_MutualFund_Manager_Info()
    #
    # # ---必要时一起更新季度报告---
    # # Automatic_Download_MutualFund_Reports(database, datetime2, startIndex=0)
    pass


# 下载明基仓位
def Download_Top_Fund_Positions(database, report_date, release_date):
    top_fund_symbol_list = get_total_top_fund_symbols(database, report_date)
    for symbol in top_fund_symbol_list:
        Download_MutualFund_Positions(database, symbol, report_date, release_date=release_date)


# 找到名基金全集全集
# 用manager_symbol 作为线索 --> 找到旗下所有基金
# 用代表基金作为线索 --> 找到基金经理manager_symbol --> 找到旗下所有基金
def get_total_top_fund_symbols(database, report_date, return_dataframe=False):
    # df_top_fund = pd.read_excel(r'C:\Users\fengshimeng3\Documents\Performance_评估模型_基金研究\基金经理50强.xlsx', sheet_name='Sheet1')
    df_top_fund = pd.read_excel(r'C:\Users\fengshimeng3\Documents\Performance_评估模型_基金研究\基金经理50强_total_stock_2021-03-31_a+b.xlsx', sheet_name='Sheet1')

    df_tenure = database.GetDataFrame("financial_data", "mutualfund_manager_tenure", filter=[("datetime2", ">=", report_date)])
    df_tenure["manager_symbol"] = df_tenure["manager_symbol"].apply(int)
    # print(df_top_fund.dtypes)
    # print(df_tenure.dtypes)

    df_top_fund.drop(columns=["manager_name", "symbol"], inplace=True)
    df_top_fund = pd.merge(df_top_fund, df_tenure, how="inner", on="manager_symbol")

    # 注意是有重复项的，明基基金有管理同一只基金的，请仔细排查
    # df_tmp = df_top_fund[df_top_fund["symbol"]=="000831.OF"]
    # print(df_tmp)
    df_top_fund.drop_duplicates("symbol", inplace=True)

    if return_dataframe:
        return df_top_fund

    top_fund_list = list(df_top_fund["symbol"])

    # 循环速度慢
    # top_manager_list = list(df_top_fund["manager_symbol"])
    # top_fund_list = []
    # count = 0
    # for manager_symbol in top_manager_list:
    #     count += 1
    #     df_tenure = database.GetDataFrame("financial_data", "mutualfund_manager_tenure",
    #                                       filter=[("manager_symbol", manager_symbol), ("datetime2", ">=", report_date)])
    #     if len(df_tenure) == 0:
    #         continue
    #     print(count, df_tenure.iloc[0]["manager_name"], manager_symbol)
    #     print(df_tenure)
    #     top_fund_list += list(df_tenure["symbol"])

    #
    return top_fund_list


# 数据库中找到缺失数值
def check_missing_top_fund_positions(database, report_date, top_fund_list):
    missing_symbol_list = []
    for mutual_symbol in top_fund_list:
        df_positions = database.GetDataFrame("financial_data", "mutualfund_positions",
                                             filter=[("mutualfund", mutual_symbol), ("report_date", report_date)])
        if df_positions.empty:
            print(mutual_symbol, report_date, "没有找到持仓")
            missing_symbol_list.append(mutual_symbol)
    #
    return missing_symbol_list


def download_missing_top_fund_positions(report_date, release_date):
    top_fund_symbol_list = get_total_top_fund_symbols(database, report_date)
    missing_symbol_list = check_missing_top_fund_positions(database, report_date, top_fund_symbol_list)
    for symbol in missing_symbol_list:
        print("Download Missing Data", symbol)
        # Download_MutualFund_Positions(database, symbol, report_date, release_date=release_date)
        a = 0


def Download_MutualFund_Manager_Info(database, cold_start=False):
    pass


def Download_MutualFund_Manager_Tenure(database, datetime_update, cold_start=False):
    df_mutualfund = database.Get_Instruments_DataFrame(instrument_type="mutualfund")
    symbols = df_mutualfund["symbol"].tolist()


def Download_MutualFund_Manager(database, datetime_update, cold_start=False):
    #
    df_mutualfund = database.Get_Instruments_DataFrame(instrument_type="mutualfund")
    symbols = df_mutualfund["symbol"].tolist()

    #
    for symbol in symbols:
        # 读取历任基金经理
        # 读取现任基金经理
        pass


if __name__ == '__main__':
    #
    # ---Connect to DataBase, Find Series 连接数据库---
    from Core.Config import *
    pathfilename = os.getcwd() + "\..\Config\config2.json"
    config = Config(pathfilename)
    database = config.DataBase("JDMySQL")
    realtime = config.RealTime()
    #
    a = THS_iFinDLogin('jd1079', '898132')

    #
    # Download_MutualFund_List(database, cold_start=False)

    # Download_MutualFund_Daily_Quote(database, datetime1=None, datetime2=None, start_index=6)
    # Test
    # symbols = ["000001.OF", "000004.OF"]
    # Download_MutualFund_Info(database, symbols)

    #
    report_date = datetime.datetime(2021, 3, 31)
    release_date = datetime.datetime(2021, 4, 23)
    #
    # Download_MutualFund_Positions(database, "000001.OF", report_date, release_date=release_date)
    # Download_Top_Fund_Positions(database, report_date, release_date)
    get_total_top_fund_symbols(database, report_date)

    # 补充不齐全的持仓
    report_date = datetime.datetime(2020, 12, 31)
    release_date = datetime.datetime(2021, 1, 21)
    # download_missing_top_fund_positions(report_date, release_date)